﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NLite.Data.Dialect.Function;
using NLite.Data.SqlTypes;
using System.Text.RegularExpressions;

namespace NLite.Data.Dialect
{
    public class JetDialect : Dialect
    {
        public override char OpenQuote
        {
            get
            {
                return '`';// or '['
            }
        }

        public override char CloseQuote
        {
            get
            {
                return '`';// or ']'
            }
        }
        //todo:The FULL JOIN is not supported by Jet database engine.
        protected override void RegisterColumnTypes()
        {
            RegisterColumnType(DbTypeCode.AnsiStringFixedLength, "CHAR(255)");
            RegisterColumnType(DbTypeCode.AnsiStringFixedLength, 255, "CHAR($l)");
            RegisterColumnType(DbTypeCode.AnsiString, "TEXT(255)");
            RegisterColumnType(DbTypeCode.AnsiString, 255, "TEXT($l)");
            RegisterColumnType(DbTypeCode.AnsiString, 1073741823, "MEMO");
            RegisterColumnType(DbTypeCode.Clob, "MEMO");

            RegisterColumnType(DbTypeCode.StringFixedLength, "CHAR(255)");
            RegisterColumnType(DbTypeCode.StringFixedLength, 255, "CHAR($l)");
            RegisterColumnType(DbTypeCode.StringFixedLength, 1073741823, "MEMO");
            RegisterColumnType(DbTypeCode.String, "TEXT(255)");
            RegisterColumnType(DbTypeCode.String, 255, "TEXT($l)");
            RegisterColumnType(DbTypeCode.String, 1073741823, "MEMO");
            RegisterColumnType(DbTypeCode.NClob, "MEMO");

            RegisterColumnType(DbTypeCode.Binary, "IMAGE");
            RegisterColumnType(DbTypeCode.Binary, 2147483647, "IMAGE");
            RegisterColumnType(DbTypeCode.Blob, "IMAGE");

            RegisterColumnType(DbTypeCode.Boolean, "BIT");
            RegisterColumnType(DbTypeCode.Byte, "BYTE");
            RegisterColumnType(DbTypeCode.Currency, "MONEY");
            RegisterColumnType(DbTypeCode.Date, "DATETIME");
            RegisterColumnType(DbTypeCode.DateTime, "DATETIME");
            RegisterColumnType(DbTypeCode.Time, "DATETIME");
           
            RegisterColumnType(DbTypeCode.Decimal, "DECIMAL(19,5)");
            RegisterColumnType(DbTypeCode.Decimal, 19, "DECIMAL(19, $l)");
            RegisterColumnType(DbTypeCode.Double, "FLOAT");
            RegisterColumnType(DbTypeCode.Guid, "GUID");
            RegisterColumnType(DbTypeCode.Int16, "SMALLINT");
            RegisterColumnType(DbTypeCode.Int32, "INT");
            RegisterColumnType(DbTypeCode.Int64, "REAL");
            RegisterColumnType(DbTypeCode.Single, "REAL");
           
            

           
        }

        public override string IdentitySelectString
        {
            get
            {
                return "select @@identity";
            }
        }

        protected override void RegisterStringFunction()
        {
            RegisterFunction(FunctionType.String.SubString, new StandardFunctionView("mid"));
            RegisterFunction(FunctionType.String.Left, new StandardFunctionView("left"));
            RegisterFunction(FunctionType.String.Right, new StandardFunctionView("right"));
            RegisterFunction(FunctionType.String.IndexOf, new StandardFunctionView("instr"));
            RegisterFunction(FunctionType.String.Concat, new VarArgsFunctionView("(", "+", ")"));
            RegisterFunction(FunctionType.String.Upper, new StandardFunctionView("ucase"));
            RegisterFunction(FunctionType.String.Lower, new StandardFunctionView("lcase"));
            RegisterFunction(FunctionType.String.Length, new StandardFunctionView("len"));
            RegisterFunction(FunctionType.String.LTrim, new StandardFunctionView("ltrim"));
            RegisterFunction(FunctionType.String.RTrim, new StandardFunctionView("rtrim"));
            RegisterFunction(FunctionType.String.Trim, new StandardFunctionView("trim"));
            RegisterFunction(FunctionType.String.Replace, new FunctionTemplateView("\"Replace(?1,?2,?3)\""));
            RegisterFunction(FunctionType.String.Remove, new RemoveFunctionView());


            //RegisterFunction(FunctionType.ToChar, new StandardFunctionView("char"));
            //RegisterFunction(FunctionType.ToNChar, new StandardFunctionView("nchar"));

            RegisterFunction(FunctionType.Case, new IIFFunctionView());
            RegisterFunction(FunctionType.Cast, new CastFunctionView());
        }

        class IIFFunctionView : IFunctionView
        {
           
            public void Render(NodeWriter visitor, params INode[] args)
            {
                var whens = (args[0] as ICollection<INode>).ToArray();
                var thens = (args[1] as ICollection<INode>).ToArray();
                var @else = args[2] as INode;

                var lenght = whens.Length;
                for (int i = 0; i < lenght; i++)
                {
                    if (i != 0)
                        visitor.Builder.Append(",");

                    visitor.Builder.Append("iif(");
                    visitor.Visit(whens[i]);
                    visitor.Builder.Append(",");
                    visitor.Visit(thens[i]);

                    if (i == lenght - 1)
                    {
                        if (@else != null)
                        {
                            visitor.Builder.Append(",");
                            visitor.Visit(@else);
                        }
                        for(var j = i;j>=0;j--)
                            visitor.Builder.Append(")");
                        
                    }


                }
            }
        }

        /// <summary>
        /// access CDec函数有缺陷，不能真正的进行类型转换，具体原因请参看：http://support.microsoft.com/kb/225931
        /// </summary>
        [Serializable]
        class CastFunctionView : IFunctionView
        {
            static Dictionary<DbTypeCode, string> functionMappings = new Dictionary<DbTypeCode, string>();
            static CastFunctionView()
            {
                functionMappings[DbTypeCode.Boolean] = "CBool({0})";
                functionMappings[DbTypeCode.Byte] = "CByte({0})";
                functionMappings[DbTypeCode.SByte] = "CByte({0})";

                functionMappings[DbTypeCode.Int16] = "CInt({0})";
                functionMappings[DbTypeCode.Int16] = "CInt({0})";
                functionMappings[DbTypeCode.Int32] = "CInt({0})";
                functionMappings[DbTypeCode.UInt32] = "CInt({0})";
                functionMappings[DbTypeCode.Int64] = "CLng({0})";
                functionMappings[DbTypeCode.UInt64] = "CLng({0})";

                functionMappings[DbTypeCode.Decimal] = "newCDec({0})";//
                functionMappings[DbTypeCode.VarNumeric] = "newCDec({0})";
                functionMappings[DbTypeCode.Currency] = "CCur  ({0})";
                functionMappings[DbTypeCode.Single] = "CSng ({0})";
                functionMappings[DbTypeCode.Double] = "CDbl({0})";

                functionMappings[DbTypeCode.Time] = "CDate({0})";
                functionMappings[DbTypeCode.Date] = "CDate({0})";
                functionMappings[DbTypeCode.DateTime] = "CDate({0})";
                functionMappings[DbTypeCode.DateTime2] = "CDate({0})";
                functionMappings[DbTypeCode.DateTimeOffset] = "CDate({0})";

                functionMappings[DbTypeCode.Guid] = "CStr({0})";
                functionMappings[DbTypeCode.AnsiString] = "CStr ({0})";
                functionMappings[DbTypeCode.AnsiStringFixedLength] = "CStr ({0})";
                functionMappings[DbTypeCode.String] = "CStr ({0})";
                functionMappings[DbTypeCode.StringFixedLength] = "CStr ({0})";
                functionMappings[DbTypeCode.Clob] = "CStr ({0})";
                functionMappings[DbTypeCode.NClob] = "CStr ({0})";
            }

            public void Render(NodeWriter visitor, params INode[] args)
            {
                if (args == null || args.Length != 2)
                {
                    throw new ArgumentException("cast() requires two arguments");
                }
                var type = args[1] as SqlObject;
                if (type == null)
                    throw new ArgumentException("cast() requires PrimitiveType argument");

                var sqlType = type.Type.DbType;
                var fnView = functionMappings[sqlType];

                var expResult = args[0].Result;
                visitor.Builder.MergeParameters(expResult);
                visitor.Builder.AppendFormat(fnView,expResult.Text);
               
            }
        }

        class RemoveFunctionView : IFunctionView
        {
            public void Render(NodeWriter visitor, params INode[] args)
            {
                if (args == null || (args.Length != 2 && args.Length != 3))
                {
                    throw new ArgumentException("remove() requires 2 or 3 arguments");
                }

                visitor.Builder.Append("\"REPLACE(");
                visitor.Visit(args[0]);
                visitor.Builder.Append(",MID(");
                visitor.Visit(args[0]);
                visitor.Builder.Append(",");
                visitor.Visit(args[1]);
                if (args.Length == 2)
                {
                    visitor.Builder.Append(",8000)");
                }
                else
                {
                    visitor.Visit(args[2]);
                    visitor.Builder.Append(")");
                }
                visitor.Builder.Append(",'')\"");
            }
        }

        protected override void RegisterMathFunction()
        {
            RegisterFunction(FunctionType.Math.Random, new NoArgFunctionView("rand"));

            RegisterFunction(FunctionType.Math.Abs, new StandardFunctionView("abs"));
           
            RegisterFunction(FunctionType.Math.Round, new StandardFunctionView("round"));
            RegisterFunction(FunctionType.Math.Cos, new StandardFunctionView("cos"));
            RegisterFunction(FunctionType.Math.Log, new StandardFunctionView("log"));
            RegisterFunction(FunctionType.Math.Sin, new StandardFunctionView("sin"));

            RegisterFunction(FunctionType.Math.Power, new FunctionTemplateView("\"power(?1)\""));
            RegisterFunction(FunctionType.Math.Acos, new FunctionTemplateView("\"acos(?1)\""));
            RegisterFunction(FunctionType.Math.Asin, new FunctionTemplateView("\"asin(?1)\""));
            RegisterFunction(FunctionType.Math.Atan, new FunctionTemplateView("\"atan(?1)\""));
            RegisterFunction(FunctionType.Math.Atan2, new FunctionTemplateView("\"atan2(?1,?2)\""));
            RegisterFunction(FunctionType.Math.Exp, new FunctionTemplateView("\"exp(?1)\""));
            RegisterFunction(FunctionType.Math.Log10, new FunctionTemplateView("\"log10(?1)\""));
            RegisterFunction(FunctionType.Math.Sqrt, new FunctionTemplateView("\"sqrt(?1)\""));
            RegisterFunction(FunctionType.Math.Tan, new FunctionTemplateView("\"tan(?1)\""));
            RegisterFunction(FunctionType.Math.Tanh, new FunctionTemplateView("\"tanh(?1)\""));
            RegisterFunction(FunctionType.Math.Truncate, new FunctionTemplateView("\"trunc(?1)\""));
            RegisterFunction(FunctionType.Math.Sign, new FunctionTemplateView("\"sign(?1)\""));
            RegisterFunction(FunctionType.Math.Ceiling, new FunctionTemplateView("\"ceiling(?1)\""));
            RegisterFunction(FunctionType.Math.Floor, new FunctionTemplateView("\"floor(?1)\""));

            RegisterFunction(FunctionType.Math.Mod, new VarArgsFunctionView("", " mod ", ""));
        }

        protected override void RegisterDateFunction()
        {
            RegisterFunction(FunctionType.DateTime.Current, new NoArgFunctionView("now"));
            RegisterFunction(FunctionType.DateTime.New, new FunctionTemplateView("CDate(?1)"));

            RegisterFunction(FunctionType.DateTime.Subtract, new FunctionTemplateView("DATEDIFF(\"d\",?1,?2)"));//返回两个日期间隔的天数

            RegisterFunction(FunctionType.DateTime.GetSecond, new StandardFunctionView("Second"));
            RegisterFunction(FunctionType.DateTime.GetMinute, new StandardFunctionView("minute"));
            RegisterFunction(FunctionType.DateTime.GetHour, new StandardFunctionView("hour"));
            RegisterFunction(FunctionType.DateTime.GetDay, new StandardFunctionView("day"));
            RegisterFunction(FunctionType.DateTime.GetMonth, new StandardFunctionView("month"));
            RegisterFunction(FunctionType.DateTime.GetYear, new StandardFunctionView("month"));

            //RegisterFunction(FunctionType.DateTime.GetMillisecond, new FunctionTemplateView("datepart(millisecond, ?1)"));
            RegisterFunction(FunctionType.DateTime.GetDayOfWeek, new FunctionTemplateView("(weekday(?1)-1)"));
            //RegisterFunction(FunctionType.DateTime.GetDayOfYear, new FunctionTemplateView("(datepart(dayofyear, ?1)-1)"));

            RegisterFunction(FunctionType.DateTime.AddSecond, new FunctionTemplateView("dateadd(\"s\", ?2,?1)"));
            RegisterFunction(FunctionType.DateTime.AddMinute, new FunctionTemplateView("dateadd(\"n\", ?2,?1)"));
            RegisterFunction(FunctionType.DateTime.AddHour, new FunctionTemplateView("dateadd(\"h\", ?2,?1)"));
            RegisterFunction(FunctionType.DateTime.AddDay, new FunctionTemplateView("dateadd(\"d\", ?2,?1)"));
            RegisterFunction(FunctionType.DateTime.AddMonth, new FunctionTemplateView("dateadd(\"m\", ?2,?1)"));
            RegisterFunction(FunctionType.DateTime.AddYear, new FunctionTemplateView("dateadd(\"yyyy\", ?2,?1)"));
        }

        public override bool SupportsCollectionOperation(CollectionOperator type)
        {
            return type == CollectionOperator.Union;
        }

        public override void BuildSelect(NodeWriter visitor, SelectNode select)
        {
            if (select == null)
                throw new ArgumentNullException("select");

            if (select.PageIndex <= 0)
                BuildBasicSelect(visitor, select);
            else
                BuildSelectWithOffset(visitor, select);

            //TODO:for update
        }

        protected virtual void BuildSelectWithOffset(NodeWriter visitor, SelectNode select)
        {
            if (select.OrderBy == null && select.OrderBy.Items.Count == 0)
            {
                throw new NotSupportedException("Access cannot support the 'skip' operation without explicit ordering");
            }
            else if (select.PageSize == -1)
            {
                throw new NotSupportedException("Access cannot support the 'skip' operation without the 'take' operation");
            }

            //throw new NotSupportedException("Access cannot support the 'skip' operation in this query");


        }

        protected void BuildBasicSelect(NodeWriter visitor, SelectNode select)
        {
            visitor.Builder.Append("SELECT ");

            if (select.OrderBy.Items.Count == 0)
            {
                if (select.IsDistinct)
                    visitor.Builder.Append("DISTINCT ");
            }

            if (select.PageSize > 0)
            {
                visitor.Builder.Append("TOP ");
                visitor.Builder.Append(select.PageSize.ToString());
                visitor.Builder.Append(" ");
            }

            if (select.Projections.Count > 0)
                visitor.VisitEnumerable(select.Projections.ToArray());
            else
                visitor.Builder.Append("*");

            if (select.Into != null)
                visitor.Visit(select.Into);

            visitor.Builder.AppendLine().Append("FROM ");
            var table = new TableNode(select.Name, select.Alias);
            table.Accept(visitor);

            if (select.Joins.Count > 0)
            {
                visitor.Builder.AppendLine();
                visitor.VisitEnumerable(select.Joins.ToArray(), " ");
            }

            select.Where.Accept(visitor);
            select.Group.Accept(visitor);
            select.Having.Accept(visitor);
            select.OrderBy.Accept(visitor);
        }
    }
}
